package it.koutifaris.travianmap.dal.sqlservices;

import static it.koutifaris.travianmap.dal.sqlservices.MapSqlContract.DB_NAME;
import it.koutifaris.travianmap.dal.sqlservices.MapSqlContract.Alliances;
import it.koutifaris.travianmap.dal.sqlservices.MapSqlContract.Players;
import it.koutifaris.travianmap.dal.sqlservices.MapSqlContract.Revisions;
import it.koutifaris.travianmap.dal.sqlservices.MapSqlContract.Villages;
import it.koutifaris.travianmap.dal.sqlservices.SqlMapUIContract.MapViewStates;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class SqlMapDbHelper extends SQLiteOpenHelper {

	private static final int DB_VERSION = 1;

	public SqlMapDbHelper(Context context, String serverId) {
		super(context, getDbName(serverId), null, DB_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(getRevisionsCreationQuery());
		db.execSQL(getAlliancesCreationQuery());
		db.execSQL(getPlayersCreationQuery());
		db.execSQL(getVillagesCreationQuery());

		db.execSQL(getMapViewStatesCreationQuery());

		db.execSQL(getVillagedIndexCreationQuery());
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	}

	public static String getDbName(String serverId) {
		return DB_NAME + "_" + serverId;
	}

	public static String getServerId(String databaseName) {
		int indexOf = databaseName.indexOf('_');
		return databaseName.substring(indexOf + 1);
	}

	private String getRevisionsCreationQuery() {
		return "CREATE TABLE " + Revisions.TABLE_NAME + " ("
				+ Revisions.COLUMN_NAME_REVISION_ID + " INTEGER PRIMARY KEY,"
				+ Revisions.COLUMN_NAME_DATE + " DATE"
				+ ");";
	}

	private String getAlliancesCreationQuery() {
		return "CREATE TABLE " + Alliances.TABLE_NAME + " ("
				+ Alliances.PK_ID + " INTEGER PRIMARY KEY,"
				+ Alliances.COLUMN_NAME_ALLIANCE_ID + " INTEGER NOT NULL,"
				+ Alliances.COLUMN_NAME_REVISION_ID + " INTEGER NOT NULL,"
				+ Alliances.COLUMN_NAME_ALLIANCE_NAME + " TEXT,"
				+ " UNIQUE (" + Alliances.COLUMN_NAME_ALLIANCE_ID + ", " + Alliances.COLUMN_NAME_REVISION_ID + ")"
				+ ");";
	}

	private String getPlayersCreationQuery() {
		return "CREATE TABLE " + Players.TABLE_NAME + " ("
				+ Players.PK_ID + " INTEGER PRIMARY KEY,"
				+ Players.COLUMN_NAME_PLAYER_ID + " INTEGER NOT NULL,"
				+ Players.COLUMN_NAME_REVISION_ID + " INTEGER NOT NULL,"
				+ Players.COLUMN_NAME_PLAYER_NAME + " TEXT,"
				+ Players.COLUMN_NAME_TRIBE + " INTEGER,"
				+ Players.COLUMN_NAME_ALLIANCE_ID + " INTEGER,"
				+ " UNIQUE (" + Players.COLUMN_NAME_PLAYER_ID + ", " + Players.COLUMN_NAME_REVISION_ID + ")"
				+ ");";
	}

	private String getVillagesCreationQuery() {
		return "CREATE TABLE " + Villages.TABLE_NAME + " ("
				+ Villages.PK_ID + " INTEGER PRIMARY KEY,"
				+ Villages.COLUMN_NAME_VILLAGE_ID + " INTEGER NOT NULL,"
				+ Villages.COLUMN_NAME_REVISION_ID + " INTEGER NOT NULL,"
				+ Villages.COLUMN_NAME_VILLAGE_NAME + " TEXT,"
				+ Villages.COLUMN_NAME_X + " INTEGER,"
				+ Villages.COLUMN_NAME_Y + " INTEGER,"
				+ Villages.COLUMN_NAME_POPULATION + " INTEGER,"
				+ Villages.COLUMN_NAME_OWNER_ID + " INTEGER,"
				+ " UNIQUE (" + Villages.COLUMN_NAME_VILLAGE_ID + ", " + Villages.COLUMN_NAME_REVISION_ID + ")"
				+ ");";
	}

	private String getMapViewStatesCreationQuery() {
		return "CREATE TABLE " + MapViewStates.TABLE_NAME + " ("
				+ MapViewStates.PK_ID + " INTEGER PRIMARY KEY,"
				+ MapViewStates.COLUMN_NAME_X + " INTEGER NOT NULL,"
				+ MapViewStates.COLUMN_NAME_Y + " INTEGER NOT NULL,"
				+ MapViewStates.COLUMN_NAME_ZOOM + " REAL NOT NULL"
				+ ");";
	}

	private String getVillagedIndexCreationQuery() {
		return "CREATE INDEX village_xy_idx ON " + Villages.TABLE_NAME +
				"(" + Villages.COLUMN_NAME_X + "," + Villages.COLUMN_NAME_Y + ")";
	}

}
